CREATE PROC [dbo].[asi_GenerateContactSalutations]
AS
BEGIN

SET NOCOUNT ON

DECLARE @sql nvarchar(2000)
DECLARE @contactKey uniqueidentifier
DECLARE @salutationKey uniqueidentifier
DECLARE @IndividualFormula nvarchar(2000)
DECLARE @InstituteFormula nvarchar(2000)

DECLARE @calcFormula nvarchar(2000)

DECLARE @tempKey uniqueidentifier
DECLARE @tempValue nvarchar(1000)


DECLARE theSalutationRefCursor CURSOR FAST_FORWARD FOR
    SELECT SalutationKey, IndividualFormula, InstituteFormula
        FROM SalutationRef
        WHERE AutoCreateFlag = 1

OPEN theSalutationRefCursor
FETCH NEXT FROM theSalutationRefCursor INTO @salutationKey, @IndividualFormula, @InstituteFormula

WHILE @@FETCH_STATUS = 0 -- spin through SalutationRef entries
BEGIN

    DECLARE theContactCursor CURSOR FAST_FORWARD FOR
        SELECT ContactKey FROM ContactMain

    OPEN theContactCursor
    FETCH NEXT FROM theContactCursor INTO @contactKey

    WHILE @@FETCH_STATUS = 0 -- spin through Contact entries
    BEGIN

        SET @tempKey = newid()

        SELECT @sql =  
            CASE
            WHEN IsInstitute = 1 THEN
                    ' asi_ProcessContactFormulaOutput '
                        + '''' + convert (nvarchar(100), @tempKey) + ''', '
                        + '''' + @InstituteFormula + ''', '
                        + '''Institute'', ''ContactKey'', '
                        + '''' + convert (nvarchar(50), @contactKey)
                        + ''''

            WHEN IsInstitute = 0 THEN
                    ' asi_ProcessContactFormulaOutput '
                        + '''' + convert (nvarchar(100), @tempKey) + ''', '
                        + '''' + @IndividualFormula + ''', '
                        + '''Individual'', ''ContactKey'', '
                        + '''' + convert (nvarchar(50), @contactKey)
                        + ''''
            END
        FROM ContactMain WHERE ContactKey = @contactKey

        EXEC (@sql)

        SELECT @tempValue = tempValue FROM tempFormula
            WHERE tempKey = @tempKey

        IF datalength(@tempValue) > 0 AND @tempValue IS NOT NULL
        BEGIN
            IF NOT EXISTS (SELECT 1 FROM ContactSalutation
                           WHERE ContactKey = @contactKey AND SalutationKey = @salutationKey)
            BEGIN
                INSERT ContactSalutation (ContactSalutationKey, IsOverridden, SalutationText, ContactKey, SalutationKey)
                VALUES (newid(), 0, @tempValue, @contactKey, @salutationKey)
            END
        END

        DELETE FROM tempFormula WHERE tempKey = @tempKey

        FETCH NEXT FROM theContactCursor INTO @contactKey
    END  -- spin through Contact entries

    CLOSE theContactCursor
    DEALLOCATE theContactCursor


    FETCH NEXT FROM theSalutationRefCursor INTO @salutationKey, @IndividualFormula, @InstituteFormula
END  -- spin through SalutationRef entries

CLOSE theSalutationRefCursor
DEALLOCATE theSalutationRefCursor

SET NOCOUNT OFF

END

GO